﻿using Newtonsoft.Json;
using NPOI.HSSF.UserModel;
using NPOI.SS.Converter;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;

namespace YMImportWeb.Common
{
    public class ExcelAccessor
    {
        //excel2003最大行和列
        public const int MAX_ROW_INDEX = 65536;
        public const int MAX_ROW_COLUMNS = 256;
        //excel2007+最大行和列
        public const int MAX_XSS_ROW_INDEX = 1048576;
        public const int MAX_XSS_ROW_COLUMNS = 16384;

        public ExcelAccessor()
        {

        }

        #region 导出 Excel 2003
        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        public byte[] ExportToByte(DataTable dtSource, string strHeaderText)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;

            HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
            dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");

            //取得列宽
            int[] arrColWidth = new int[dtSource.Columns.Count];
            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            int rowIndex = 0;

            foreach (DataRow row in dtSource.Rows)
            {
                if (rowIndex > MAX_ROW_COLUMNS) { continue; }

                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == MAX_ROW_COLUMNS || rowIndex == 0)
                {
                    if (rowIndex != 0) { sheet = workbook.CreateSheet() as HSSFSheet; }

                    #region 表头及样式
                    {
                        HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
                        headerRow.HeightInPoints = 25;
                        headerRow.CreateCell(0).SetCellValue(strHeaderText);

                        HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
                        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                        HSSFFont font = workbook.CreateFont() as HSSFFont;
                        font.FontHeightInPoints = 20;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);

                        headerRow.GetCell(0).CellStyle = headStyle;

                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                        //headerRow.Dispose();
                    }
                    #endregion

                    #region 列头及样式
                    {
                        HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
                        HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
                        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                        HSSFFont font = workbook.CreateFont() as HSSFFont;
                        font.FontHeightInPoints = 10;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);

                        foreach (DataColumn column in dtSource.Columns)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                            int _temp_width = (arrColWidth[column.Ordinal] + 1);
                            _temp_width = (_temp_width > 100 ? 100 : _temp_width);
                            _temp_width = (_temp_width < 5 ? 5 : _temp_width);

                            //设置列宽
                            sheet.SetColumnWidth(column.Ordinal, (_temp_width) * 256);
                        }
                        //headerRow.Dispose();
                    }
                    #endregion

                    rowIndex = 2;
                }
                #endregion

                #region 填充内容
                HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
                foreach (DataColumn column in dtSource.Columns)
                {
                    SetCellValue(dateStyle, row[column], dataRow.CreateCell(column.Ordinal));
                }
                #endregion

                rowIndex++;
            }

            byte[] buf;
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                buf = ms.ToArray();
                ms.Flush();
            }
            return buf;
        }

        /// <summary>
        /// List数据导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源List</param>
        /// <param name="strHeaderText">表头文本</param>
        public byte[] ExportToByte(List<List<object>> dtSource, string strHeaderText)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;

            HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            //取得列宽
            int[] arrColWidth = new int[dtSource.First().Count];
            foreach (var item in dtSource.First())
            {
                arrColWidth[dtSource.First().IndexOf(item)] = Encoding.GetEncoding(936).GetBytes(item.ToString()).Length;
            }
            for (int i = 0; i < dtSource.Count; i++)
            {
                for (int j = 0; j < dtSource.First().Count; j++)
                {
                    var item = (dtSource[i][j] ?? "").ToString();

                    int intTemp = Encoding.GetEncoding(936).GetBytes(item).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        intTemp = (intTemp < 5 ? 5 : intTemp);
                        arrColWidth[j] = (intTemp > 100 ? 100 : intTemp);
                    }
                }
            }
            int rowIndex = 0;

            foreach (var row in dtSource)
            {
                if (rowIndex > MAX_ROW_COLUMNS) { continue; }

                #region 新建表，填充表头，填充列头，样式
                //第一个list是列名
                //if (rowIndex == 65535 || rowIndex == 0)
                if (row == dtSource.First())
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet() as HSSFSheet;
                    }

                    #region 表头及样式
                    {
                        HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
                        headerRow.HeightInPoints = 25;
                        headerRow.CreateCell(0).SetCellValue(strHeaderText);

                        HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
                        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                        HSSFFont font = workbook.CreateFont() as HSSFFont;
                        font.FontHeightInPoints = 20;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);

                        headerRow.GetCell(0).CellStyle = headStyle;

                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, row.Count - 1));
                        //headerRow.Dispose();
                    }
                    #endregion

                    #region 列头及样式
                    {
                        HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
                        HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
                        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                        HSSFFont font = workbook.CreateFont() as HSSFFont;
                        font.FontHeightInPoints = 10;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);

                        foreach (var column in row)
                        {
                            var _column_index = row.IndexOf(column);
                            headerRow.CreateCell(_column_index).SetCellValue(column.ToString());
                            headerRow.GetCell(_column_index).CellStyle = headStyle;

                            int _temp_width = (arrColWidth[_column_index] + 1);
                            _temp_width = (_temp_width > 100 ? 100 : _temp_width);
                            _temp_width = (_temp_width < 5 ? 5 : _temp_width);

                            //设置列宽
                            sheet.SetColumnWidth(_column_index, (_temp_width) * 256);
                        }
                        //headerRow.Dispose();
                    }
                    #endregion

                    rowIndex = 2;
                    //next
                    continue;
                }
                #endregion

                #region 填充内容
                HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
                for (var i = 0; i < row.Count; i++)
                {
                    SetCellValue(dateStyle, row[i], dataRow.CreateCell(i));
                }
                #endregion

                rowIndex++;
            }
            byte[] buf;
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                buf = ms.ToArray();
                ms.Flush();
            }
            return buf;
        }
        #endregion

        #region 导出 Excel 2007+
        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        public byte[] ExportXSSToByte(DataTable dtSource, string strHeaderText)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet() as ISheet;

            ICellStyle dateStyle = workbook.CreateCellStyle() as ICellStyle;
            IDataFormat format = workbook.CreateDataFormat() as IDataFormat;
            dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");

            //取得列宽
            int[] arrColWidth = new int[dtSource.Columns.Count];
            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        intTemp = (intTemp < 5 ? 5 : intTemp);
                        arrColWidth[j] = (intTemp > 100 ? 100 : intTemp);
                    }
                }
            }
            int rowIndex = 0;

            foreach (DataRow row in dtSource.Rows)
            {
                if (rowIndex > MAX_XSS_ROW_COLUMNS) { continue; }

                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == MAX_XSS_ROW_COLUMNS || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet() as ISheet;
                    }

                    #region 表头及样式
                    {
                        IRow headerRow = sheet.CreateRow(0) as IRow;
                        headerRow.HeightInPoints = 25;
                        headerRow.CreateCell(0).SetCellValue(strHeaderText);

                        ICellStyle headStyle = workbook.CreateCellStyle() as ICellStyle;
                        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                        IFont font = workbook.CreateFont() as IFont;
                        font.FontHeightInPoints = 20;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);

                        headerRow.GetCell(0).CellStyle = headStyle;

                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                        //headerRow.Dispose();
                    }
                    #endregion

                    #region 列头及样式
                    {
                        IRow headerRow = sheet.CreateRow(1) as IRow;
                        ICellStyle headStyle = workbook.CreateCellStyle() as ICellStyle;
                        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                        IFont font = workbook.CreateFont() as IFont;
                        font.FontHeightInPoints = 10;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);

                        foreach (DataColumn column in dtSource.Columns)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                            int _temp_width = (arrColWidth[column.Ordinal] + 1);
                            _temp_width = (_temp_width > 100 ? 100 : _temp_width);
                            _temp_width = (_temp_width < 5 ? 5 : _temp_width);

                            //设置列宽
                            sheet.SetColumnWidth(column.Ordinal, (_temp_width) * 256);
                        }
                        //headerRow.Dispose();
                    }
                    #endregion

                    rowIndex = 2;
                }
                #endregion

                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex) as IRow;
                foreach (DataColumn column in dtSource.Columns)
                {
                    SetCellValue(dateStyle, row[column], dataRow.CreateCell(column.Ordinal));
                }
                #endregion

                rowIndex++;
            }

            byte[] buf;
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                buf = ms.ToArray();
                ms.Flush();
            }
            return buf;
        }

        /// <summary>
        /// List数据导出到Excel的byte[]
        /// </summary>
        /// <param name="dtSource">源List</param>
        /// <param name="strHeaderText">表头文本</param>
        public byte[] ExportXSSToByte(List<List<object>> dtSource, string strHeaderText)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet();

            ICellStyle dateStyle = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");

            //取得列宽
            int[] arrColWidth = new int[dtSource.First().Count];
            foreach (var item in dtSource.First())
            {
                arrColWidth[dtSource.First().IndexOf(item)] = Encoding.GetEncoding(936).GetBytes(item.ToString()).Length;
            }
            for (int i = 0; i < dtSource.Count; i++)
            {
                for (int j = 0; j < dtSource.First().Count; j++)
                {
                    var item = (dtSource[i][j] ?? "").ToString();

                    int intTemp = Encoding.GetEncoding(936).GetBytes(item).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        intTemp = (intTemp < 5 ? 5 : intTemp);
                        arrColWidth[j] = (intTemp > 100 ? 100 : intTemp);
                    }
                }
            }
            int rowIndex = 0;

            foreach (var row in dtSource)
            {
                if (rowIndex > MAX_XSS_ROW_COLUMNS) { continue; }

                #region 新建表，填充表头，填充列头，样式
                if (row == dtSource.First())
                {
                    if (rowIndex != 0) { sheet = workbook.CreateSheet(); }

                    #region 表头及样式
                    {
                        IRow headerRow = sheet.CreateRow(0);
                        headerRow.HeightInPoints = 25;
                        headerRow.CreateCell(0).SetCellValue(strHeaderText);

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 20;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);

                        headerRow.GetCell(0).CellStyle = headStyle;

                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, row.Count - 1));
                        //headerRow.Dispose();
                    }
                    #endregion

                    #region 列头及样式
                    {
                        IRow headerRow = sheet.CreateRow(1);
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 10;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);

                        foreach (var column in row)
                        {
                            var _column_index = row.IndexOf(column);
                            headerRow.CreateCell(_column_index).SetCellValue(column.ToString());
                            headerRow.GetCell(_column_index).CellStyle = headStyle;

                            int _temp_width = (arrColWidth[_column_index] + 1);
                            _temp_width = (_temp_width > 100 ? 100 : _temp_width);
                            _temp_width = (_temp_width < 5 ? 5 : _temp_width);

                            //设置列宽
                            sheet.SetColumnWidth(_column_index, (_temp_width) * 256);
                        }
                        //headerRow.Dispose();
                    }
                    #endregion

                    rowIndex = 2;
                    //next
                    continue;
                }
                #endregion

                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);
                for (var i = 0; i < row.Count; i++)
                {
                    SetCellValue(dateStyle, row[i], dataRow.CreateCell(i));
                }
                #endregion

                rowIndex++;
            }

            byte[] buf;
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                buf = ms.ToArray();
                ms.Flush();
            }
            return buf;
        }

        /// <summary>
        /// 设置单元格的值
        /// </summary>
        /// <param name="dateStyle"></param>
        /// <param name="column"></param>
        /// <param name="newCell"></param>
        /// <param name="drValue"></param>
        private void SetCellValue(ICellStyle dateStyle, object cellValue, ICell newCell)
        {
            if (cellValue == null)
            {
                newCell.SetCellValue(string.Empty);
                return;
            }
            var drValue = cellValue.ToString();
            switch (cellValue.GetType().ToString())
            {
                case "System.String": //字符串类型
                    double result;
                    if (isNumeric(drValue, out result))
                    {
                        double.TryParse(drValue, out result);
                        newCell.SetCellValue(result);
                        break;
                    }
                    else
                    {
                        newCell.SetCellValue(drValue);
                        break;
                    }
                case "System.Guid": //Guid类型
                    Guid guidV;
                    Guid.TryParse(drValue, out guidV);
                    newCell.SetCellValue(guidV.ToString());
                    break;
                case "System.DateTime": //日期类型
                    DateTime dateV;
                    DateTime.TryParse(drValue, out dateV);
                    newCell.SetCellValue(dateV);
                    newCell.CellStyle = dateStyle; //格式化显示
                    break;
                case "System.Boolean": //布尔型
                    bool boolV = false;
                    bool.TryParse(drValue, out boolV);
                    newCell.SetCellValue(boolV);
                    break;
                case "System.Int16": //整型
                case "System.Int32":
                case "System.Int64":
                case "System.Byte":
                    int intV = 0;
                    int.TryParse(drValue, out intV);
                    newCell.SetCellValue(intV);
                    break;
                case "System.Decimal": //浮点型
                case "System.Double":
                    double doubV = 0;
                    double.TryParse(drValue, out doubV);
                    newCell.SetCellValue(doubV);
                    break;
                case "System.DBNull": //空值处理
                    newCell.SetCellValue(string.Empty);
                    break;
                default:
                    newCell.SetCellValue(string.Empty);
                    break;
            }
        }

        public bool isNumeric(String message, out double result)
        {
            Regex rex = new Regex(@"^[-]?\d+[.]?\d*$");
            result = -1;
            if (rex.IsMatch(message))
            {
                result = double.Parse(message);
                return true;
            }
            else
                return false;
        }
        #endregion

        #region 读取Excel
        /// <summary>
        /// 读取excel
        /// <para>默认第一行为标头</para>
        /// </summary>
        /// <param name="fs">文件流</param>
        /// <returns></returns>
        public DataTable ReadExcelByStream(Stream st, int headerRowIndex = 0)
        {
            if (st == null || st.Length == 0) { return null; }

            DataTable dt = new DataTable();
            IWorkbook wb = WorkbookFactory.Create(st);

            ISheet sheet = wb.GetSheetAt(0);
            dt = ReadExcelToDataTable(sheet, headerRowIndex, true);
            return dt;
        }

        /// <summary>
        /// 将制定sheet中的数据导出到datatable中
        /// </summary>
        /// <param name="sheet">需要导出的sheet</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <returns></returns>
        private DataTable ReadExcelToDataTable(ISheet sheet, int headerRowIndex, bool needHeader)
        {
            DataTable table = new DataTable();
            IRow headerRow;
            int cellCount;
            try
            {
                if (headerRowIndex < 0 || !needHeader)
                {
                    headerRow = sheet.GetRow(0);
                    cellCount = headerRow.LastCellNum;

                    for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
                    {
                        DataColumn column = new DataColumn(Convert.ToString(i));
                        table.Columns.Add(column);
                    }
                }
                else
                {
                    headerRow = sheet.GetRow(headerRowIndex);
                    cellCount = headerRow.LastCellNum;

                    for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
                    {
                        if (headerRow.GetCell(i) == null)
                        {
                            if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
                            {
                                DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
                                table.Columns.Add(column);
                            }
                            else
                            {
                                DataColumn column = new DataColumn(Convert.ToString(i));
                                table.Columns.Add(column);
                            }
                        }
                        else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
                        {
                            DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
                            table.Columns.Add(column);
                        }
                        else
                        {
                            //特殊的列名替换
                            var text = headerRow.GetCell(i).ToString();
                            DataColumn column = new DataColumn(text);
                            table.Columns.Add(column);
                        }
                    }
                }
                int rowCount = sheet.LastRowNum;
                for (int i = (headerRowIndex + 1); i <= sheet.LastRowNum; i++)
                {
                    try
                    {
                        IRow row;
                        if (sheet.GetRow(i) == null)
                        {
                            row = sheet.CreateRow(i);
                        }
                        else
                        {
                            row = sheet.GetRow(i);
                        }

                        DataRow dataRow = table.NewRow();

                        for (int j = row.FirstCellNum; j <= cellCount; j++)
                        {
                            try
                            {
                                if (row.GetCell(j) != null)
                                {
                                    switch (row.GetCell(j).CellType)
                                    {
                                        case CellType.String:
                                            string str = row.GetCell(j).StringCellValue;
                                            if (str != null && str.Length > 0)
                                            {
                                                dataRow[j] = str.ToString();
                                            }
                                            else
                                            {
                                                dataRow[j] = null;
                                            }
                                            break;
                                        case CellType.Numeric:
                                            if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
                                            {
                                                dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
                                            }
                                            else
                                            {
                                                dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
                                            }
                                            break;
                                        case CellType.Boolean:
                                            dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
                                            break;
                                        case CellType.Error:
                                            dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
                                            break;
                                        case CellType.Formula:
                                            switch (row.GetCell(j).CachedFormulaResultType)
                                            {
                                                case CellType.String:
                                                    string strFORMULA = row.GetCell(j).StringCellValue;
                                                    if (strFORMULA != null && strFORMULA.Length > 0)
                                                    {
                                                        dataRow[j] = strFORMULA.ToString();
                                                    }
                                                    else
                                                    {
                                                        dataRow[j] = null;
                                                    }
                                                    break;
                                                case CellType.Numeric:
                                                    dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
                                                    break;
                                                case CellType.Boolean:
                                                    dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
                                                    break;
                                                case CellType.Unknown:
                                                    dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
                                                    break;
                                                default:
                                                    dataRow[j] = "";
                                                    break;
                                            }
                                            break;
                                        default:
                                            dataRow[j] = "";
                                            break;
                                    }
                                }
                            }
                            catch (Exception exception)
                            {
                                LogHelper.Error(exception.ToString());
                            }
                        }
                        table.Rows.Add(dataRow);
                    }
                    catch (Exception exception)
                    {
                        LogHelper.Error(exception.ToString());
                    }
                }
            }
            catch (Exception exception)
            {
                LogHelper.Error(exception.ToString());
            }
            return table;
        }

        /// <summary>
        /// 读取excel,从文件流中(上传,第一行为表头)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="st"></param>
        /// <returns></returns>
        public T ReadExcelByStream<T>(Stream st, int headerRowIndex = 0)
        {
            if (st == null || st.Length == 0) { return default(T); }

            var dt = ReadExcelByStream(st, headerRowIndex);
            //简单的方法
            var _obj_str = JsonConvert.SerializeObject(dt);
            return JsonConvert.DeserializeObject<T>(_obj_str);
        }
        #endregion

        #region ExcelToHtml
        /// <summary>
        /// 将excel工作薄转换为html字节
        /// </summary>
        /// <param name="workbook"></param>
        /// <returns></returns>
        public byte[] XLSConvertToHtmlByte(IWorkbook workbook)
        {
            ExcelToHtmlConverter excelToHtmlConverter = new ExcelToHtmlConverter();

            //set output parameter
            excelToHtmlConverter.OutputColumnHeaders = true;
            excelToHtmlConverter.OutputHiddenColumns = false;
            excelToHtmlConverter.OutputHiddenRows = false;
            excelToHtmlConverter.OutputLeadingSpacesAsNonBreaking = false;
            excelToHtmlConverter.OutputRowNumbers = true;
            excelToHtmlConverter.UseDivsToSpan = false;

            //process the excel file
            excelToHtmlConverter.ProcessWorkbook(workbook);

            //output the html file
            byte[] buf;
            using (MemoryStream ms = new MemoryStream())
            {
                excelToHtmlConverter.Document.Save(ms);
                buf = ms.ToArray();
                ms.Flush();
            }
            return buf;
        }

        /// <summary>
        /// 将excel工作薄转换为html字符串
        /// </summary>
        /// <param name="workbook"></param>
        /// <returns></returns>
        public string XLSConvertToHtml(IWorkbook workbook)
        {
            string _html = System.Text.Encoding.UTF8.GetString(this.XLSConvertToHtmlByte(workbook));
            string _css = "<style type=\"text/css\">table{background:#ccc;border:1px solid !important;}table td{background:#fff;border:1px solid !important;padding:2px 4px;white-space:normal !important;}</style>";
            _html = _html.Insert(_html.IndexOf("</head>"), _css);
            return _html;
        }
        /// <summary>
        /// 将文件流(excel)转换为html
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public string XLSConvertToHtml(System.IO.Stream s)
        {
            return this.XLSConvertToHtml(NPOI.SS.UserModel.WorkbookFactory.Create(s));
        }
        /// <summary>
        /// 将文件流(excel)转换为html
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public string XLSConvertToHtml(byte[] buf)
        {
            return this.XLSConvertToHtml(new MemoryStream(buf));
        }
        #endregion
    }
}